package com.dream.wukong.common.core.importexport.excel;

import com.dream.wukong.common.core.importexport.csv.parser.AbstractParser;
import com.dream.wukong.common.core.util.StringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author: wuyichao
 * @Description:
 * @Date: Created in 9:55 2018/9/19.
 * @Modified:
 */
public class ExcelWriter extends AbstractParser {

    private static final Logger logger = LoggerFactory.getLogger(ExcelWriter.class);


    public ExcelWriter() {
        super("ExcelWriter");
        this.setEncoding("gbk");
    }


    @Override
    public List<String[]> parseRows(File input) {
        InputStream is;
        try {
            is = new FileInputStream(input);

            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            List<String[]> list = new ArrayList<String[]>(10000);
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                int celNum =  hssfSheet.getRow(0).getPhysicalNumberOfCells();
                // 循环行Row
                for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    String [] arr = new String[celNum];
                    for(int i = 0;i<celNum;i++){
                        arr[i] = getValue(hssfRow.getCell(i));
                    }
                    list.add(arr);
                }
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public File writeRows(List<Object[]> rows) {
        File tmpFile;
        try {
            tmpFile = File.createTempFile(UUID.randomUUID().toString(), ".xls");
            writeRows(rows, tmpFile.getAbsolutePath());
        } catch (IOException e) {
            logger.error(e.getMessage());
            return null;
        }
        return tmpFile;
    }
    @Override
    public File writeRows(List<Object[]> rows, String filepath) {
        File file = new File(filepath);
        try {
            writeRows(rows,file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return file;
    }


    public void writeRows(List<Object[]> rows ,File file) throws Exception  {
        FileOutputStream fout = new FileOutputStream(file);
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(file.getName());
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        for (int j = 0 ; j<rows.get(0).length ; j++){
            HSSFCell cell = row.createCell((short) j);
            cell.setCellValue(StringUtil.asString(rows.get(0)[j]));
            cell.setCellStyle(style);
        }
        for (int i = 1; i < rows.size(); i++) {
            row = sheet.createRow((int) i);
            Object[] data = rows.get(i);
            for (int j = 0 ; j<data.length ; j++) {
                Object o = rows.get(i)[j];
                if(o instanceof Integer){
                    int intValue = (Integer) o;
                    row.createCell((short) j).setCellValue(intValue);
                }  else if (o instanceof Long) {
                    long longValue = (Long) o;
                    row.createCell((short) j).setCellValue(longValue);
                }else if(o instanceof Boolean){
                    Boolean boolValue = (Boolean) o;
                    row.createCell((short) j).setCellValue(boolValue);
                }else{
                    row.createCell((short) j).setCellValue(StringUtil.asString(o));
                }
            }
        }
        wb.write(fout);
        wb.close();
        fout.close();
    }

    @Override
    public void processRows(File input) {

    }

    /**
     * 得到Excel表中的值
     *
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        String cellValue = null;
        DecimalFormat df = new DecimalFormat("#");
        if(StringUtil.isNotEmpty(hssfCell)){

            switch (hssfCell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = hssfCell.getRichStringCellValue().getString().trim();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
                        //用于转化为日期格式
                        Date d = hssfCell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formater.format(d).toString();
                    }else{
                        cellValue = df.format(hssfCell.getNumericCellValue()).toString();
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(hssfCell.getBooleanCellValue()).trim();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    cellValue = hssfCell.getCellFormula();
                    break;
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }




    public static void main(String[] args) throws IOException {
        List<Object[]> rows = Arrays
                .asList(new Object[][] {
                        { "1997", "Ford", "E350", "ac, abs, moon", "3000.00" },
                        { "1999", "Chevy", "Venture \"Extended Edition\"", "",
                                "1" },
                        { "1996", "Jeep", "Grand中文 Cherokee",
                                "MUST SELL!\nair, moon roof, loaded", "4799.00" },
                        {},
                        { "1999", "Chevy",
                                "Venture \"Extended Edition, Very Large\"",
                                null, "5000.00" },
                        { null, "", "Venture \"Extended Edition\"", null,
                                "4900.00" }, });
        ExcelWriter ExcelWriter = new ExcelWriter();
        File file = ExcelWriter.writeRows(rows);
        System.out.println(file.getAbsolutePath());
    }

}